﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Diagnostics;
using System.Data.OleDb;
using You2V.Data.Shared;

namespace You2V.LocalDBLayer
{
    public class ExcelAccess
    {
        public static StudentInfo2012 GetStudentFromExcel(string strFileName, ref string ErrorMessage)
        {
            StudentInfo2012 student = new StudentInfo2012();
            if (student.FamilyMemberList == null)
                student.FamilyMemberList = new List<FamilyMember>();

            student.FamilyMemberList.Add(new FamilyMember());
            student.FamilyMemberList.Add(new FamilyMember());
            student.FamilyMemberList.Add(new FamilyMember());
            student.FamilyMemberList.Add(new FamilyMember());
            student.FamilyMemberList.Add(new FamilyMember());
            try
            {
                DataTable dt = ExcelAccess.GetDataTableFromExcel(strFileName, "学生资料");

                if (dt != null)
                {
                    bool VerifiedVersion = false;
                    bool fileFinish = false;
                    foreach (DataRow row in dt.Rows)
                    {
                        string ItemName = ReadString(row, "项目名");

                        string Value = ReadString(row, "取值");

                        //Debug.WriteLine(ItemName + ":\t\t" + Value);

                        if (ItemName.Equals("模板版本"))
                        {
                            if (Value.Equals("1.0.0001"))
                            {
                                VerifiedVersion = true;
                            }
                            else
                            {
                                ErrorMessage = "文档模板版本错误 3920";
                                return null;
                            }
                        }
                        else if (ItemName.Equals("注意事项") 
                                || ItemName.IndexOf("以下填写家属信息") >= 0)
                        {
                            continue;   //不做任何事情
                        }
                        else if (ItemName.Equals("文件结尾"))
                        {
                            fileFinish = true;
                        }
                        else
                        {
                            FillDataFromDT(ref student, ItemName, Value);
                        }

                    }
                    if (!VerifiedVersion)
                    {
                        ErrorMessage = "无法核实模板版本 错误5359";
                        return null;
                    }
                    else if(!fileFinish)
                    {
                        ErrorMessage = "无法找到文件结束标志, 错误 3982";
                        return null;
                    }
                    else
                    {
                        student.CleanEmptyFamilyMemberData();
                        return student;
                    }
                }
                else
                {
                    ErrorMessage = "打开Excel错误 503294";
                    return null;
                }
            }
            catch
            {
            }
            ErrorMessage = "打开Excel错误 392059";
            return null;
        }

        private static DataTable GetDataTableFromExcel(string strFileName, string SheetName)        //参数为文件路径+文件名。就是DataSource
        {
            if (strFileName != "")
            {
                string conn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strFileName + ";Extended Properties=Excel 8.0";
                string sql = "select * from [" + SheetName + "$]";
                OleDbDataAdapter da = new OleDbDataAdapter(sql, conn);
                DataSet ds = new DataSet();
                try
                {
                    da.Fill(ds, "datatable");

                    if (ds != null && ds.Tables != null && ds.Tables.Count > 0)
                    {
                        DataTable dt = ds.Tables[0];
                        return dt;
                    }
                }
                catch
                {

                }
                return null;
            }
            else
            {
                return null;
            }
        }


        private static string ReadString(DataRow row, string ColumnName)
        {
            if (row[ColumnName].GetType() == typeof(System.DBNull))
                return string.Empty;

            return (string)row[ColumnName];
        }

        public static Area GetAreaFromName(string areaName)
        {
            if (You2VDataDictionary.AreaList != null)
            {
                foreach (Area Area in You2VDataDictionary.AreaList)
                {
                    if (Area.areaname.Equals(areaName))
                    {
                        return Area;
                    }
                }
            }
            return null;
        }

        private static void FillDataFromDT(ref StudentInfo2012 student, string ItemName, string Value)
        {
            if (string.IsNullOrWhiteSpace(ItemName))
                return;

            if (string.IsNullOrWhiteSpace(Value))
            {
                Debug.WriteLine("跳过没有值的列\t" + ItemName + "\t" + Value);
                return;
            }

            switch (ItemName)
            {
                case "地区":
                    student.StudentArea = GetAreaFromName(Value);
                    break;

                case "学生编号":
                    student.StudentNumber = Value;
                    break;

                case "学生全名":
                    student.FullName = Value;
                    break;

                case "学生别名":
                    student.NickName = Value;
                    break;

                case "性别":
                    student.Gender = Value;
                    break;

                case "出生年月日":
                    try
                    {
                        int Date = Convert.ToInt32(Value);
                        if (Date >= 19000101 && Date < 30000101)    //公元3000年，哈哈
                        {
                            int Year = Date / 10000;
                            int Month = (Date / 100) % 100;
                            int Day = Date % 100;

                            DateTime dob = new DateTime(Year, Month, Day);
                            student.BirthDate = dob;
                        }
                    }
                    catch
                    {
                        student.BirthDate = DateTime.MinValue;
                    }
                    break;


                case "是否住校":
                    student.LiveInSchoolString = Value;
                    break;

                case "民族":
                    student.Nation = Value;
                    break;

                case "学校全名":
                    //todo, 学校简称如何填写
                    if (student.SchoolInfo == null)
                        student.SchoolInfo = new SchoolInfo();

                    student.SchoolInfo.SchoolFullName = Value;
                    break;

                case "班级":
                    student.ClassName = Value;
                    break;

                case "邮编":
                    if (student.StudentAddress == null)
                        student.StudentAddress = new Address();

                    student.StudentAddress.PostCode = Value;
                    break;

                case "学生通信地址":
                    if (student.StudentAddress == null)
                        student.StudentAddress = new Address();

                    student.StudentAddress.AddressText = Value;
                    break;

                case "学生电子邮件":
                    student.EmailAddress = Value;
                    break;

                case "家庭详细地址":
                    student.HomeDetailAddressText = Value;
                    break;

                case "对外发布家庭地址":
                    student.HomeAddressTextForPublish = Value;
                    break;

                case "家庭地址邮编":
                    student.HomeAddressPostCode = Value;
                    break;

                case "家庭收入":
                    student.HomeIncomeSummary = Value;
                    break;

                case "住房情况":
                    student.HouseStatus = Value;
                    break;

                case "财产概况":
                    student.AssetStatus = Value;
                    break;

                case "建议资助标准":
                    student.SponsorSuggestion = Value;
                    break;

                case "曾获奖励":
                    student.RewardsJustGain = Value;
                    break;

                case "家访志愿者":
                    student.Volunteer = Value;
                    break;

                case "家访报告":
                    student.VolunteerReport = Value;
                    break;

                case "学生自述":
                    student.SelfIntroduction = Value;
                    break;

                case "班主任评语":
                    student.TeacherFeedback = Value;
                    break;

                case "家属1关系":
                    student.FamilyMemberList[0].RelationShip = Value;
                    break;

                case "家属1姓名":
                    student.FamilyMemberList[0].FullName = Value;
                    break;

                case "家属1年龄":
                    student.FamilyMemberList[0].Age = Value;
                    break;

                case "家属1职业":
                    student.FamilyMemberList[0].Job = Value;
                    break;

                case "家属1健康状况":
                    student.FamilyMemberList[0].HealthStatus = Value;
                    break;

                case "家属1外出打工情况":
                    student.FamilyMemberList[0].JobOutOfHometown = Value;
                    break;

                case "家属2关系":
                    student.FamilyMemberList[1].RelationShip = Value;
                    break;

                case "家属2姓名":
                    student.FamilyMemberList[1].FullName = Value;
                    break;

                case "家属2年龄":
                    student.FamilyMemberList[1].Age = Value;
                    break;

                case "家属2职业":
                    student.FamilyMemberList[1].Job = Value;
                    break;

                case "家属2健康状况":
                    student.FamilyMemberList[1].HealthStatus = Value;
                    break;

                case "家属2外出打工情况":
                    student.FamilyMemberList[1].JobOutOfHometown = Value;
                    break;

                case "家属3关系":
                    student.FamilyMemberList[2].RelationShip = Value;
                    break;

                case "家属3姓名":
                    student.FamilyMemberList[2].FullName = Value;
                    break;

                case "家属3年龄":
                    student.FamilyMemberList[2].Age = Value;
                    break;

                case "家属3职业":
                    student.FamilyMemberList[2].Job = Value;
                    break;

                case "家属3健康状况":
                    student.FamilyMemberList[2].HealthStatus = Value;
                    break;

                case "家属3外出打工情况":
                    student.FamilyMemberList[2].JobOutOfHometown = Value;
                    break;

                case "家属4关系":
                    student.FamilyMemberList[3].RelationShip = Value;
                    break;

                case "家属4姓名":
                    student.FamilyMemberList[3].FullName = Value;
                    break;

                case "家属4年龄":
                    student.FamilyMemberList[3].Age = Value;
                    break;

                case "家属4职业":
                    student.FamilyMemberList[3].Job = Value;
                    break;

                case "家属4健康状况":
                    student.FamilyMemberList[3].HealthStatus = Value;
                    break;

                case "家属4外出打工情况":
                    student.FamilyMemberList[3].JobOutOfHometown = Value;
                    break;

                case "家属5关系":
                    student.FamilyMemberList[4].RelationShip = Value;
                    break;

                case "家属5姓名":
                    student.FamilyMemberList[4].FullName = Value;
                    break;

                case "家属5年龄":
                    student.FamilyMemberList[4].Age = Value;
                    break;

                case "家属5职业":
                    student.FamilyMemberList[4].Job = Value;
                    break;

                case "家属5健康状况":
                    student.FamilyMemberList[4].HealthStatus = Value;
                    break;

                case "家属5外出打工情况":
                    student.FamilyMemberList[4].JobOutOfHometown = Value;
                    break;

                default:
                    Debug.WriteLine("未处理的列:\t" + ItemName + "\t\t" + Value);
                    break;
            }
        }

    }
}
